Imports MyClasses
Imports System.Data.SqlClient
Imports System.Text
Imports System.Configuration

Public Class DPurchaseReturn
    Dim objLocPurchase As CPurchase
    Dim intPurchaseID As Integer = 0
    Dim SeqPurchaseID As String = 0
    Public Function Save(ByRef ObjPurchase As CPurchase, ByRef objPurchaseItem() As CNGItem, ByRef dtbOtherCharges As DataTable, ByVal dtbTaxes As DataTable, ByVal intVoucherID As Integer) As Integer
        Dim dbConnection As New dbAccess
        Dim intResult As Integer = 0

        Try
            objLocPurchase = ObjPurchase
            dbConnection.BeginTransaction()
            If intVoucherID > 0 Then
                intPurchaseID = intVoucherID
                UpdatePurchase(ObjPurchase, dbConnection, intVoucherID)
            Else
                intPurchaseID = SavePurchase(ObjPurchase, dbConnection)

            End If
            If intPurchaseID > 0 Then
                If intVoucherID > 0 Then
                    If objLocPurchase.PurchaseType <> DGeneral.DocTypes.PURCHASE_ORDER Then
                        If UpdatePurchaseItemDetails(intVoucherID, dbConnection) = 0 Then
                            intResult = 0
                            Exit Function
                        End If
                    End If
                    '
                    intResult = dbConnection.ExecuteNonQuery("DELETE FROM tPurchaseDet WHERE PurchaseID=" & intPurchaseID.ToString())
                End If
                objLocPurchase.PurchaseBillID = intPurchaseID
                intResult = SavePurchaseItemDetail(intPurchaseID, objPurchaseItem, dbConnection)

            End If
            ' If intResult = 0 Then Exit Function
            'If funPriSaveTaxes(dtbTaxes, dbConnection) = 0 Then
            '    intResult = 0
            '    Exit Function
            'End If
            'If SaveOtherCharges(dtbTaxes, dbConnection) = 0 Then
            '    intResult = 0
            '    Exit Function
            'End If
            'SaveOtherCharges(dtbOtherCharges, dbConnection)
            'If ObjPurchase.PartyID > 0 AndAlso ObjPurchase.PaidAmt > 0 Then
            '    Dim Payment(0) As CPayment
            '    Payment(0) = New CPayment
            '    With Payment(0)
            '        .PayType = "Recp"
            '        .PayerID = ObjPurchase.PartyID
            '        .PayDate = Now.Date()
            '        .PayMode = 1
            '        .PayAmount = ObjPurchase.PaidAmt
            '        .Concession = 0
            '        .Description = "CASH CUMSTOMER Purchase"
            '        .BillID = intPurchaseID
            '    End With
            '    '    intResult = DPayment.SavePayment(Payment, "S", dbConnect ion)
            'End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "Save")
            intResult = 0
        Finally
            If intResult = 0 Then
                intPurchaseID = 0
                dbConnection.RollBackTransaction()
            Else
                dbConnection.CommitTransaction()
            End If
            dbConnection.closeSqlcon()
            If intPurchaseID = 0 Then
                Save = 0
            Else
                If intVoucherID > 0 Then
                    Save = intVoucherID
                Else
                    Save = SeqPurchaseID
                End If
            End If
            'Save = intPurchaseID
        End Try
    End Function

    Public Sub updateBilledStatus()
        DbHelper.ExecuteNonQuery("UPDATE tPurchase SET Billed=1 WHERE PurchaseID=" & intPurchaseID.ToString)
    End Sub
    Private Function funPriSaveTaxes(dtbTax As DataTable, ByRef dbConnection As dbAccess) As Int16
        dbConnection.ExecuteNonQuery("DELETE FROM tPurchaseTax WHERE PurchaseID=" & intPurchaseID.ToString())
        Dim param(4) As SqlParameter
        Dim strSql As String = "INSERT INTO tPurchaseTax(PurchaseID,TaxID,TAX,TaxableAmt,TaxAmt)" & _
                                              "VALUES(@PurchaseID,@TaxID,@TAX,@TaxableAmt,@TaxAmt)"
        Dim intResult As Integer
        For Each taxRow As DataRow In dtbTax.Rows
            If taxRow.Item(2) > 0 Then

                param(0) = New SqlParameter("@PurchaseID", intPurchaseID)
                param(1) = New SqlParameter("@TaxID", taxRow.Item(0))
                param(2) = New SqlParameter("@TAX", taxRow.Item(1))
                param(3) = New SqlParameter("@TaxableAmt", taxRow.Item(2))
                param(4) = New SqlParameter("@TaxAmt", taxRow.Item(3))
                intResult += dbConnection.ExecuteNonQuery(strSql, param)
            End If

        Next
        Return intResult
    End Function
    Public Function SavePurchase(ByRef ObjPurchase As CPurchase, ByRef dbConnection As dbAccess) As Integer

        Dim strSql As String = ""
        Dim param(19) As SqlParameter
        Try
            strSql = "INSERT INTO tPurchase(SeqPurchaseID,PartyCode,PO_GRNID,PurchaseDate,PromoterID,SiteID,Amount,OtherCharges,TotalTaxes,Discount,GrossAmount,ShipingAddress,Status,CU,CD,LU,LD,FinYrID,PurchaseType,PriceListID)" & _
                     "VALUES(@SeqPurchaseID,@PartyCode,@PO_GRNID,@PurchaseDate,@PromoterID,@SiteID,@Amount,@OtherCharges,@TotalTaxes,@Discount,@GrossAmount,@ShipingAddress,@Status,@CU,@CD,@LU,@LD,@FinYrID,@PurchaseType,@PriceListID)"
            ''''''''''''''''ShipingAddress,Status,CU,CD,LU,LD,FinYrID,PurchaseType

            'SeqPurchaseID = DGeneral.GetSeqNum(IIf(ObjPurchase.PurchaseType = DGeneral.DocTypes.INVOICE, DGeneral.DocTypes.CHALLAN, ObjPurchase.PurchaseType), dbConnection, True)
            SeqPurchaseID = DGeneral.GetSeqNum(ObjPurchase.PurchaseType, dbConnection, True)
            param(0) = New SqlParameter("@SeqPurchaseID", SeqPurchaseID)
            param(1) = New SqlParameter("@PartyCode", ObjPurchase.PartyID)
            param(2) = New SqlParameter("@PO_GRNID", ObjPurchase.PO_GRNID)
            param(3) = New SqlParameter("@PurchaseDate", ObjPurchase.PurchaseDate)
            param(4) = New SqlParameter("@PromoterID", ObjPurchase.PromoterID)
            param(5) = New SqlParameter("@SiteID", ObjPurchase.Site)
            param(6) = New SqlParameter("@Amount", ObjPurchase.Amount)
            param(7) = New SqlParameter("@OtherCharges", ObjPurchase.dblOtherCharges)
            param(8) = New SqlParameter("@TotalTaxes", ObjPurchase.TotalTax)
            param(9) = New SqlParameter("@Discount", ObjPurchase.Discount)
            param(10) = New SqlParameter("@GrossAmount", ObjPurchase.GrossAmt)
            param(11) = New SqlParameter("@ShipingAddress", ObjPurchase.ShippingAdress)
            param(12) = New SqlParameter("@Status", "1") 'ACTIVE
            param(13) = New SqlParameter("@CU", CCommon.GetUserID)
            param(14) = New SqlParameter("@CD", Date.Now.Date())
            param(15) = New SqlParameter("@LU", CCommon.GetUserID)
            param(16) = New SqlParameter("@LD", Date.Now.Date())
            param(17) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)
            param(18) = New SqlParameter("@PurchaseType", ObjPurchase.PurchaseType)
            param(19) = New SqlParameter("@PriceListID", ObjPurchase.priceListID)
            If dbConnection.ExecuteNonQuery(strSql, param) > 0 Then

                Return dbConnection.ExecuteScalar("SELECT TOP 1 PurchaseID FROM tPurchase ORDER BY PurchaseID DESC")
            Else
                Return 0
            End If


        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchase")
            Return 0
        End Try

    End Function
    Public Shared Function UpdatePurchase(ByRef ObjPurchase As CPurchase, ByRef dbConnection As dbAccess, ByVal intVoucherID As Integer) As Integer

        Dim strSql As String = ""
        Dim param(12) As SqlParameter
        Try
            strSql = "UPDATE tPurchase SET PartyCode=@PartyCode,PromoterID=@PromoterID,SiteID=@SiteID,Amount=@Amount,OtherCharges=@OtherCharges,TotalTaxes=@TotalTaxes,Discount=@Discount,GrossAmount=@GrossAmount,ShipingAddress=@ShipingAddress,LU=@LU,LD=@LD,PriceListID=@PriceListID WHERE PurchaseID=@PurchaseID"

            param(0) = New SqlParameter("@PartyCode", ObjPurchase.PartyID)
            param(1) = New SqlParameter("@PromoterID", ObjPurchase.PromoterID)
            param(2) = New SqlParameter("@SiteID", ObjPurchase.Site)
            param(3) = New SqlParameter("@Amount", ObjPurchase.Amount)
            param(4) = New SqlParameter("@OtherCharges", ObjPurchase.dblOtherCharges)
            param(5) = New SqlParameter("@TotalTaxes", ObjPurchase.TotalTax)
            param(6) = New SqlParameter("@Discount", ObjPurchase.Discount)
            param(7) = New SqlParameter("@GrossAmount", ObjPurchase.GrossAmt)
            param(8) = New SqlParameter("@ShipingAddress", ObjPurchase.ShippingAdress)

            param(9) = New SqlParameter("@LU", CCommon.GetUserID)
            param(10) = New SqlParameter("@LD", Date.Now.Date())
            param(11) = New SqlParameter("@PriceListID", ObjPurchase.priceListID)
            param(12) = New SqlParameter("@PurchaseID", intVoucherID)

            Return dbConnection.ExecuteNonQuery(strSql, param)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchase")
            Return 0
        End Try

    End Function

    Public Function UpdatePurchaseItemDetails(ByVal PurchaseID As Integer, ByRef dbConnection As dbAccess) As Integer

        Dim dtDItem As DataTable = GetPurchaseBillItems(PurchaseID)
        Dim objNgItem As CNGItem = New CNGItem
        Dim i As Integer
        Try
            For Each PurchaseItem As DataRow In dtDItem.Rows

                If objLocPurchase.PurchaseType <> DGeneral.DocTypes.PURCHASE_ORDER Then
                    objNgItem.isTinTin = PurchaseItem.Item("IsTinTin")
                    objNgItem.Quantity = PurchaseItem.Item("Qty")
                    objNgItem.intPackUpdateID = PurchaseItem.Item("PackUpdateIDPK")
                    If DGeneral.SubstractNGItemStock(objNgItem, dbConnection) = 0 Then
                        Return 0
                    End If
                End If
            Next
            Return i
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchaseItemDetail", "")
            Return 0
        End Try
    End Function

    Public Function SavePurchaseItemDetail(ByVal PurchaseID As Integer, ByRef objPurchaseItem() As CNGItem, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = String.Empty
        Dim param(13) As SqlParameter

        Dim intRecordsUpdate As Integer
        Dim i As Integer
        Try
            For Each Item As CNGItem In objPurchaseItem
                strSql = " INSERT INTO tPurchaseDet(PurchaseID,ItemID,CLRcode,PackUpdateID,PackSizeID,PurchasePrice,Qty,Amount,DISC,DISC_AMT,TAXID,TAXAMT,SchemeDiscount,FinYrId)" & _
                         " VALUES(@PurchaseID,@ItemID,@CLRcode,@PackUpdateID,@PackSizeID,@PurchasePrice,@Qty,@Amount,@DISC,@DISC_AMT,@TAXID,@TAXAMT,@SchemeDiscount,@FinYrId)"

                param(0) = New SqlParameter("@PurchaseID", PurchaseID)
                param(1) = New SqlParameter("@ItemID", Item.ItemID)
                param(2) = New SqlParameter("@CLRcode", Item.ColorCode)
                param(3) = New SqlParameter("@PackUpdateID", Item.intPackUpdateID)
                param(4) = New SqlParameter("@PackSizeID", Item.intPackSizeID)
                param(5) = New SqlParameter("@PurchasePrice", Item.dblRate)
                param(6) = New SqlParameter("@Qty", Item.Quantity)
                param(7) = New SqlParameter("@Amount", Item.Amount)
                param(8) = New SqlParameter("@DISC", Item.strDisc)
                param(9) = New SqlParameter("@DISC_AMT", Item.dblDISCAmt)
                param(10) = New SqlParameter("@TAXID", Item.TaxID)
                param(11) = New SqlParameter("@TAXAMT", Item.TaxAmt)
                param(12) = New SqlParameter("@SchemeDiscount", Item.SchemeDiscount)
                param(13) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)

                i = dbConnection.ExecuteNonQuery(strSql, param)
                If i = 0 Then
                    Return i
                End If

                If objLocPurchase.PurchaseType <> DGeneral.DocTypes.PURCHASE_ORDER Then

                    If DGeneral.AddNGItemStock(Item, dbConnection) = 0 Then
                        Return 0
                    End If
                End If
                intRecordsUpdate += i

                'If ConfigurationManager.AppSettings("TAX") = 1 Then 'CHECK FOR TAX (T0)
                '    SavePurchaseTaxDetails(PurchaseID, Item.ItemID, Item.CLRcode, Item.Amount, dbConnection)
                'End If
                ' ClearCounterItem(Item.CounterSeqID, dbConnection)
                'DPurchaseInvoice.SaveStockRegister(Item.ItemID, Item.Quantity, Now.Date, "S", dbConnection)
            Next
            Return i
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchaseItemDetail", strSql)
            Return 0
        End Try
    End Function
    'Private Function subpriUpdatePurchaseStock(intPackUpdateID As Integer, intQty As Integer, ByRef dbConnection As dbAccess) As Integer
    '    Dim strSql As String = "UPDATE PackingUpdateDetail SET Stock=Stock +" & intQty.ToString() & " WHERE ID=" + intPackUpdateID.ToString()

    '    Return dbConnection.ExecuteNonQuery(strSql)

    'End Function
    'Private Function subpriUpdatePurchaseReturnStock(intPackUpdateID As Integer, intQty As Integer, ByRef dbConnection As dbAccess) As Integer
    '    Dim strSql As String = "UPDATE PackingUpdateDetail SET Stock=Stock - " & intQty.ToString() & " WHERE ID=" + intPackUpdateID.ToString()

    '    Return dbConnection.ExecuteNonQuery(strSql)

    'End Function

    Private Function SaveOtherCharges(ByRef dtbOtherCharges As DataTable, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = "DELETE FROM tPurchaseOtherCharges WHERE PurchaseID=" + intPurchaseID.ToString()
        If dbConnection.ExecuteNonQuery(strSql) = 0 Then

        End If
        For Each drCharge As DataRow In dtbOtherCharges.Rows

            strSql = "INSERT INTO tPurchaseOtherCharges(PurchaseID,OtherChargeID,Value,Amount) VALUES(" & intPurchaseID.ToString & "," & drCharge.Item(0).ToString & ",'" & drCharge.Item(2).ToString & "'," & drCharge.Item(3).ToString & ") "
            If dbConnection.ExecuteNonQuery(strSql) = 0 Then
                Return 0
            End If
        Next
        Return 1
    End Function


    Public Shared Function SavePurchaseTaxDetails(ByVal BillID As Long, ByVal ItemID As Long, ByVal CLRcode As String, ByVal Amount As Double, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = ""
        Dim param(8) As SqlParameter
        Dim intResult As Integer
        Try
            Dim dtDItemTaxes As New DataTable ' = dtDItemTaxes(
            dtDItemTaxes = DTax.GetItemTaxes(ItemID, CLRcode, Amount)
            If IsNothing(dtDItemTaxes) Then Return 1
            For Each Tax As DataRow In dtDItemTaxes.Rows

                strSql = " INSERT INTO tBillItemTax(BillItemSeqid,ItemId,BillID,BillType,CLRcode, " & _
                                     " FormulaId,TaxID,TaxPercent,TaxAmount) " & _
                                     " VALUES (@BillItemSeq,@ItemID,@BillID,@BillType,@CLRcode,@FormulaId,@TaxID,@TaxPerc,@TaxAmount)"

                param(0) = New SqlParameter("@BillItemSeq", DGeneral.GetSeqNum("BillItemTax", dbConnection, True))
                param(1) = New SqlParameter("@ItemID", ItemID)
                param(2) = New SqlParameter("@BillID", BillID)
                param(3) = New SqlParameter("@BillType", "S")
                param(4) = New SqlParameter("@CLRcode", CLRcode)
                param(5) = New SqlParameter("@FormulaID", "")
                param(6) = New SqlParameter("@TaxID", Tax("ID"))
                param(7) = New SqlParameter("@TaxPerc", Tax("Val"))
                param(8) = New SqlParameter("@TaxAmount", Tax("Amount"))
                intResult = intResult + dbConnection.ExecuteNonQuery(strSql, param)
            Next

            Return intResult

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchaseTaxDetails", strSql)
            Return 0
        End Try
    End Function


    'Public Shared Function SavePurchaseReturn(ByVal SeqReturnID As String, ByVal ReturnDate As Date, ByVal InvoiceID As Integer, ByVal Amount As Double, ByRef Items() As CReturnItem, ByVal Remark As String) As Integer
    '    Dim strSql As String = ""
    '    Dim param(8) As SqlParameter
    '    Dim dbConnection As New dbAccess
    '    Dim intReturnID As Integer
    '    Dim intResult As Integer
    '    Try
    '        dbConnection.BeginTransaction()

    '        strSql = "INSERT INTO tPurchaseReturn(PurchaseReturnID,SeqPurchaseReturnID,PurchaseID,PurchaseReturnDate,Amount,Remark,LU,LD,FinYrId) " & _
    '         " VALUES(@PurchaseReturnID,@SeqPurchaseReturnID,@PurchaseID,@PurchaseReturnDate,@Amount,@Remark,@LU,@LD,@FinYrId)"

    '        intReturnID = DGeneral.GetSeqNum("SR", dbConnection, True)
    '        param(0) = New SqlParameter("@PurchaseReturnID", intReturnID)
    '        param(1) = New SqlParameter("@SeqPurchaseReturnID", SeqReturnID)
    '        param(2) = New SqlParameter("@PurchaseID", InvoiceID)
    '        param(3) = New SqlParameter("@PurchaseReturnDate", ReturnDate)
    '        param(4) = New SqlParameter("@Amount", Amount)
    '        'param(5) = New SqlParameter("@Remark", Remark)
    '        'Modified By:-(2008/01/16)
    '        param(5) = New SqlParameter("@Remark", IIf(IsNothing(Remark), "", Remark))
    '        param(6) = New SqlParameter("@LU", CCommon.GetUserID)
    '        param(7) = New SqlParameter("@LD", Date.Now())
    '        param(8) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)
    '        intResult = dbConnection.ExecuteNonQuery(strSql, param)

    '        If intResult = 0 Then Exit Function
    '        intResult = SavePurchaseReturnItems(intReturnID, Items, dbConnection)

    '        If intResult = 0 Then Exit Function
    '        intResult = DPurchaseInvoice.UpdtePurchaseReturnStock(Items, ReturnDate, dbConnection)

    '    Catch ex As Exception
    '        dbConnection.RollBackTransaction()
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchaseReturn", strSql)
    '        intResult = 0
    '    Finally

    '        If intResult > 0 Then
    '            dbConnection.CommitTransaction()
    '        Else
    '            intResult = 0
    '            dbConnection.RollBackTransaction()
    '        End If
    '        dbConnection.closeSqlcon()
    '        SavePurchaseReturn = intReturnID
    '    End Try

    'End Function

    Public Shared Function SavePurchaseReturnItems(ByVal ReturnID As Integer, ByRef Items() As CReturnItem, ByRef dbConnection As dbAccess) As Integer

        Dim strSql As String
        Dim param(7) As SqlParameter
        Dim i As Integer

        Try

            For Each Item As CReturnItem In Items
                If Not IsNothing(Item) Then
                    strSql = " INSERT INTO tPurchaseReturnDet(PurchaseReturnID,ItemID,CLRcode,Qty,ReturnPrice,Amount,Reason,FinYrId)" & _
                                              " VALUES(@PurchaseReturnID,@ItemID,@CLRcode,@Qty,@ReturnPrice,@Amount,@Reason,@FinYrId)"

                    param(0) = New SqlParameter("@PurchaseReturnID", ReturnID)
                    param(1) = New SqlParameter("@ItemID", Item.ItemID)
                    param(2) = New SqlParameter("@CLRcode", Item.CLRcode)
                    param(3) = New SqlParameter("@Qty", Item.Quantity)
                    param(4) = New SqlParameter("@ReturnPrice", Item.Rate)
                    param(5) = New SqlParameter("@Amount", Item.Amount)
                    'param(6) = New SqlParameter("@Reason", Item.Reason)
                    'Modified By:-(2008/01/16)
                    param(6) = New SqlParameter("@Reason", IIf(IsNothing(Item.Reason), "", Item.Reason))
                    param(7) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)

                    i = i + dbConnection.ExecuteNonQuery(strSql, param)
                End If
            Next
            Return i

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchaseReturnItems")
            Return 0
        End Try
    End Function

#Region "GETDETAILS"
    'Public Shared Function GetPurchaseBillList() As DataTable
    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT PurchaseID,SeqPurchaseID AS VoucherNo,PurchaseDate,Amount,mMiscCd.CodeDesc +' '+ FName +' '+MName +' '+LName AS Name ," & _
    '                 " mCustomer.PartyCode FROM tPurchase, mPerson, mCustomer, mmisccd " & _
    '                 " WHERE  tPurchase.PartyCode <>0 AND tPurchase.status ='A' AND mPerson.PersonID=mCustomer.PersonID" & _
    '                 " AND tPurchase.PartyCode=mCustomer.PartyCode AND mMiscCd.CodeType='TITLE' AND mMiscCd.CodeID=mPerson.Title" & _
    '                 " UNION " & _
    '                 " SELECT PurchaseID,SeqPurchaseID AS VoucherNo,PurchaseDate,Amount," & _
    '                 " title +' '+ first_name +' '+middle_name +' '+last_name AS Name ,mCustomer.PartyCode " & _
    '                 " FROM tPurchase, mPatient, mCustomer WHERE  tPurchase.PartyCode <>0 AND tPurchase.status ='A'  AND patient_id=mCustomer.PatientID " & _
    '                 " AND tPurchase.PartyCode=mCustomer.PartyCode "

    '        '            "SELECT PurchaseID,SeqPurchaseID AS VoucherNo,PurchaseDate,Amount,DispName as Name ,mCustomer.PartyCode FROM tPurchase,mPerson,mCustomer " & _
    '        '            "WHERE  tPurchase.PartyCode <>0 AND tPurchase.status ='A' AND mPerson.PersonID=mCustomer.PersonID " & _
    '        '            "AND tPurchase.PartyCode=mCustomer.PartyCode " & _
    '        '            "UNION " & _
    '        '            "SELECT PurchaseID,SeqPurchaseID AS VoucherNo,PurchaseDate,Amount,title +' '+ first_name +' '+middle_name +' '+last_name AS Name ,mCustomer.PartyCode " & _
    '        '            "FROM tPurchase, mPatient, mCustomer " & _
    '        '            "WHERE  tPurchase.PartyCode <>0 AND tPurchase.status ='A'  AND patient_id=mCustomer.PatientID " & _
    '        '            "AND tPurchase.PartyCode=mCustomer.PartyCode "

    '        Return DbHelper.ExecuteDataTable(strSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "ClearCounterItem", strSql)
    '        Return Nothing
    '    End Try
    'End Function
    Public Shared Function GetPurchaseBill(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.SeqPurchaseID,S.PartyCode AS PartyID ,ISNULL(P.PartyCode,'') AS PartyCode,ISNULL(P.PartyName,'') AS PartyName, S.PurchaseDate,S.PromoterID,ISNULL(R.PromoterName,'') AS PromoterName, ISNULL(R.PromoterCode,'') AS PromoterCode ,S.SiteID,S.PriceListID,S.Amount,S.OtherCharges,S.TotalTaxes,S.Discount,S.GrossAmount,S.ShipingAddress,S.PurchaseType,ISNULL(T.SiteName,'') AS SiteName,ISNULL(T.SiteCode,'') AS SiteCode  FROM tPurchase S LEFT OUTER JOIN Party P ON(S.PartyCode = P.ID) LEFT OUTER JOIN Promoter R ON (S.PromoterID= R.ID) LEFT OUTER JOIN [Site] T ON(S.SiteID=T.ID) WHERE S.PurchaseID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBill", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetPurchaseBillItems(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID as PackUpdateIDPK, ISNULL(D.GodownId,0) AS GodownId,ISNULL(D.PackingID,0) AS PackingID,S.PurchasePrice,S.Qty,S.Amount,S.DISC,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,ISNULL(G.GodownName,'') AS GodownName ,ISNULL(PK.packSize + ' ' + PK.PackingDescription,'') AS PCK FROM tPurchaseDet S INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID)LEFT OUTER  JOIN PackingUpdateDetail D ON(D.ID=S.PAckUpdateID)LEFT OUTER JOIN PackingSize PK ON (D.PackSizeID=PK.ID) LEFT OUTER JOIN Godown G ON(D.GodownID=G.ID) WHERE S.PurchaseID=" & intSearchID.ToString()
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBillItems", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetPOBillItems(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, 0 AS PackUpdateID AS PackUpdateIDPK,0 AS GodownId,ISNULL(S.PackSizeID,0) AS PackingID,S.PurchasePrice,S.Qty,S.Amount,S.DISC,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,'' AS GodownName ,ISNULL(PK.packSize + ' ' + PK.PackingDescription,'') AS PCK FROM tPurchaseDet S INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID) LEFT OUTER JOIN PackingSize PK ON (S.PackSizeID=PK.ID) WHERE S.PurchaseID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBillItems", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetPOBillItems(ByVal strSearchIDs As String) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, 0 AS PackUpdateID  AS PackUpdateIDPK ,0 AS GodownId,ISNULL(S.PackSizeID,0) AS PackingID,S.PurchasePrice,S.Qty,S.Amount,S.DISC,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,'' AS GodownName ,ISNULL(PK.packSize + ' ' + PK.PackingDescription,'') AS PCK FROM tPurchaseDet S INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID) LEFT OUTER JOIN PackingSize PK ON (S.PackSizeID=PK.ID) WHERE S.PurchaseID IN (" & strSearchIDs & ")"

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBillItems", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetPurchaseBillItems(ByVal strSearchIDs As String) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID  AS PackUpdateIDPK,D.GodownId,D.PackingID,S.PurchasePrice,S.Qty,S.Amount,S.DISC,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,G.GodownName,PK.packSize + ' ' + PK.PackingDescription AS PCK FROM tPurchaseDet S INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID)INNER JOIN PackingUpdateDetail D ON(D.ID=S.PAckUpdateID)INNER JOIN PackingSize PK ON (D.PackSizeID=PK.ID) INNER JOIN Godown G ON(D.GodownID=G.ID) WHERE S.PurchaseID  IN ( " & strSearchIDs & " )"

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseBillItems", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetPurchaseTaxes(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.TaxID,S.TAX,S.TaxableAmt,S.TaxAmt, TaxAmt+ TaxableAmt AS TOTAL FROM tPurchaseTax S WHERE S.PurchaseID=" & intSearchID.ToString() & " UNION SELECT Id AS TAXID,TaxPercentage AS TAX,'0' AS TAXABLEAMT,'0' AS TAXAMT,'0' AS TOTAL FROM TAX WHERE IsDeleted=0 AND ID NOT IN(SELECT DISTINCT S.TaxID FROM  tPurchaseTax S WHERE S.PurchaseID=" & intSearchID.ToString() & ")"

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetPurchaseTaxes", strSql)
            Return Nothing
        End Try
    End Function


    Public Shared Function GetPurchaseBillOtherCharges(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT O.ID,O.Chargename,ISNULL(S.Value,0)AS Value,ISNULL(S.Amount,0) AS Amount,O.IsTaxable FROM tPurchaseOtherCharges S RIGHT OUTER JOIN OtherCharge O ON (S.OtherChargeID=O.ID) WHERE O.IsDeleted =0 AND O.AddToInvoice =1 AND S.PurchaseID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "ClearCounterItem", strSql)
            Return Nothing
        End Try
    End Function


    Public Shared Function GetItemUnit(ByVal ItemID As Integer, ByVal CLRcode As String) As String
        Try
            Return DbHelper.ExecuteScalar("SELECT SellUnit FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetItemUnit")
            Return ""
        End Try
    End Function

    Public Shared Function GetItemPackUnit(ByVal ItemID As Integer, ByVal CLRcode As String) As DataTable
        Try
            Return DbHelper.ExecuteDataTable("SELECT SellPack,SellUnit,ExpDate,ConversionFactor FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetItemPackUnit")
            Return Nothing
        End Try
    End Function

    'Public Shared Function GetCounterData(ByVal CounterID As Integer) As DataTable
    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT SeqID,ItemID,CLRcode,PurchaseUnit,PurchasePrice,Qty,Amount,SchemeID,schemeItemQty,SchemeDiscount,FreeReplacement,StockUpdate,Status FROM wPurchaseDet WHERE CounterID=" & CounterID & " Order by SeqID,Status"
    '        Return DbHelper.ExecuteDataTable(strSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetCounterData", strSql)
    '        Return Nothing
    '    End Try
    'End Function

    Public Shared Function GetWItem(ByVal WorkingSeqID As Integer, ByRef dbConnection As dbAccess) As DataTable
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT  ItemID,CLRcode,Qty,Amount,schemeItemQty,StockID FROM wPurchaseDet WHERE SeqID=" & WorkingSeqID
            Return dbConnection.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetWItem", StrSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetWItem(ByVal WorkingSeqID As Integer) As DataTable
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT  ItemID,CLRcode,Qty,Amount,schemeItemQty,StockID FROM wPurchaseDet WHERE SeqID=" & WorkingSeqID
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetWItem", StrSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetWItemDetail(ByVal WorkingSeqID As Integer) As String
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT StockID FROM wPurchaseDet WHERE SeqID=" & WorkingSeqID
            Return DbHelper.ExecuteScalar(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetWItemDetail", StrSql)
            Return ""
        End Try
    End Function

    Public Shared Function GetWItemsDetails(ByVal WorkingSeqID As String) As DataTable
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT ItemID ,CLRcode,Qty ,SchemeID,schemeItemQty,VendorID,StockID FROM wPurchaseDet WHERE SeqID IN(" & WorkingSeqID & ")"
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetWItemsDetails", StrSql)
            Return Nothing
        End Try
    End Function

    ' 3/12/2007
    'Public Shared Function GetCounterDetails() As DataTable

    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT mCounter.CounterID,COUNT(ItemID) AS Items,SUM(amount)AS Amount  " & _
    '                "FROM wPurchaseDet INNER JOIN mCounter ON mCounter.CounterID=wPurchaseDet.CounterID  " & _
    '                "WHERE wPurchaseDet.status IN ('W','U') AND mCounter.IsVisibile=1 GROUP BY mCounter.CounterID "
    '        Return DbHelper.ExecuteDataTable(strSql)

    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetCounterDetails", strSql)
    '        Return Nothing
    '    End Try
    'End Function

    'Public Shared Function GetCounterSummary() As DataTable

    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT M.CounterID AS Counter,(Select SUM(Amt) FROM    " & _
    '                 "(Select CounterID,SUM(AMOUNT) amt FROM tPurchase WHERE PartyCode=0 AND  " & _
    '                 "DATEDIFF(D,CD,GETDATE())=0  AND CounterID=M.CounterID AND FinalBillID<>'F' GROUP BY CounterID " & _
    '                 "UNION " & _
    '                 "SELECT CounterID,SUM(Amount) amt FROM wPurchasedet where Status IN ('W','F','U') AND " & _
    '                 "DATEDIFF(D,CD,GETDATE())=0  AND CounterID=M.CounterID GROUP BY CounterID) X Group by CounterID " & _
    '                 ") as 'Counter Purchase', " & _
    '                 "(SELECT SUM(S.Amount) FROM tPurchase S WHERE " & _
    '                 "DATEDIFF(D,S.PurchaseDate,GETDATE())=0 AND CounterID=M.CounterID AND CUSTID>0 AND FinalBillID<>'F' " & _
    '                 "GROUP BY CounterID) AS 'Regular Purchase',(SELECT COUNT(S.PurchaseID) " & _
    '                 "FROM tPurchase S where DATEDIFF(D,S.PurchaseDate,GETDATE())=0 AND CounterID=M.CounterID AND CUSTID>0 AND FinalBillID<>'F' " & _
    '                 "GROUP BY CounterID) AS 'Total Bills' FROM MCOUNTER M"

    '        Return DbHelper.ExecuteDataTable(strSql)

    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetCounterSummary", strSql)
    '        Return Nothing
    '    End Try
    'End Function

    'Public Shared Function GetCounterUpdates(ByVal intCounterID) As DataTable
    '    'Returns only Counters with no OF items & Amount of Perticular Counter
    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT Sum(W.Amount) AS 'Amount',count(w.CounterID)as 'Items'" & _
    '                 "FROM wPurchaseDet W WHERE " & _
    '                 "W.Status IN ('W','U')  AND  W.CounterID=" & intCounterID & _
    '                 " GROUP BY W.CounterID ORDER BY W.CounterID"

    '        Return DbHelper.ExecuteDataTable(strSql)

    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetCounterSummary")
    '        Return Nothing
    '    End Try
    'End Function

    'Public Shared Function GetItemBatchVendors(ByVal ItemID As Integer, ByVal CLRcode As String) As DataTable
    '    'Returns only Counters with no OF items & Amount of Perticular Counter
    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT VendorID,Quantity FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & " '"

    '        Return DbHelper.ExecuteDataTable(strSql)

    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetCounterSummary")
    '        strSql = Nothing
    '        Return Nothing
    '    End Try

    'End Function
    'Written By:- (20071023)    'Selecting Search Record
    Public Shared Function GetSearchItems(ByVal SellRecNo As Integer) As DataTable
        Dim StrSql As String
        Try
            StrSql = "SELECT sdet.PurchaseReturnID,sdet.ItemID,sdet.CLRcode,sdet.Qty,sdet.ReturnPrice,sdet.Amount,sdet.Reason," & _
                     " mp.seqitemid,mp.itemname, " & _
                     " tsr.PurchaseID,tsr.PurchaseReturnDate,tsr.Amount,tsr.Remark, " & _
                     " sdet.ItemID, sdet.CLRcode" & _
                     " FROM tPurchaseReturnDet sdet,tPurchaseReturn tsr,mpitem mp " & _
                     " WHERE mp.itemid=sdet.itemid AND tsr.PurchaseReturnID=sdet.PurchaseReturnID AND " & _
                     " sdet.PurchaseReturnID=" & SellRecNo

            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetSearchItems")
            Return Nothing
        End Try
    End Function

    'Written By:- (20071029)    'Select Holiday Date    ' Query Modified By  
    'Public Shared Function IsHoliday() As Integer
    '    Dim StrSql As String
    '    Try
    '        'StrSql = "SELECT COUNT(*) FROM mholidays WHERE holidaydate='" & CurDate & "'"
    '        StrSql = "SELECT COUNT(*) FROM mHolidays WHERE DateDiff (D,getdate(),Holidaydate) =0"
    '        Return DbHelper.ExecuteScalar(StrSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "IsHoliday")
    '        Return Nothing
    '    End Try
    'End Function

    'Public Shared Function GetCounterAmount(ByVal CounterNo As Byte) As Double
    '    Try
    '        Dim strSql As String = "SELECT SUM (Amount) as Amount FROM wPurchaseDet WHERE CounterID=" & CounterNo
    '        Return DbHelper.ExecuteScalar(strSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetCounterAmount")
    '        Return 0
    '    End Try
    'End Function
    'Public Shared Function CheckForSchedule(ByVal intCounters As Byte) As Integer
    '    Try
    '        Dim strSql As String = "SELECT I.ScheduleDrug,warnMessage " & _
    '                               "FROM wPurchaseDet W INNER JOIN mpItem I ON W.ItemID=I.ItemID " & _
    '                               "LEFT OUTER JOIN mWarning ON  mWarning.warnValue=I.ScheduleDrug " & _
    '                               "WHERE W.CounterID<=" & intCounters & _
    '                               " AND WarnModule='Purchase' AND warnBase='SCHEDULE' "
    '        If DbHelper.ExecuteDataTable(strSql).Rows.Count > 0 Then
    '            Return 1
    '        Else : Return 0
    '        End If
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "CheckForSchedule")
    '        Return 0
    '    End Try
    'End Function

    Public Shared Function GetStockIDS(ByVal CounterSeqID As Integer, ByRef dbConnection As dbAccess) As String
        Dim strStocks As New StringBuilder
        Try
            'COMMA SEPAREATED  'StockID,Quantity,SchemeItemQty VALUES
            Dim strVAL As String
            strVAL = dbConnection.ExecuteScalar("SELECT StockID FROM wPurchaseDet WHERE SeqID=" & CounterSeqID)
            Dim strStockDtls() As String = strVAL.Split(",")

            For Each Value As String In strStockDtls
                If Value = "" Then Exit For
                strStocks.Append(Mid(Value, 1, InStr(Value, "-", ) - 1) & ",")
            Next
            If strStocks.Length > 0 Then strStocks.Replace(",", "", strStocks.Length - 1, 1)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetStockIDS")
            strStocks = Nothing
        Finally
            GetStockIDS = strStocks.ToString()
        End Try
    End Function
#End Region '"GETDETAILS"
    Public Shared Function GetParty() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT P.Id,P.PartyCode,P.PartyName FROM [NGSPaintsDB].[dbo].Party P WHERE P.IsDeleted=0")
    End Function
    Public Shared Function GetPromoter() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT P.Id,P.PromoterCode,P.PromoterName FROM [NGSPaintsDB].[dbo].promoter P WHERE P.IsDeleted=0")
    End Function
    Public Shared Function GetColors() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id],[ColorCode],[ColorDescription] FROM [NGSPaintsDB].[dbo].[Color] WHERE [IsDeleted]=0")
    End Function

    Public Shared Function GetColors(ByVal intColumnIndex As Integer, ByRef RecordCount As Integer, ByVal intProductID As Integer) As SqlDataReader
        'Returns The ItemList used for Purchase Item List
        Dim StrSql As String = ""
        Try

            'StrSql = "SELECT  mpItem.ItemID,SeqItemID AS  Code,ItemName AS  Name,mfctrShortName AS  Company," & _
            '             " ISNULL((select SUM(Quantity ) FROM tStock WHERE DateDiff (D,getdate(),tStock.ExpDate)  >=0 AND tStock.ItemID = mpItem.ItemID  ),0) AS  Stock," & _
            '             " 0 LocationCode,mpItem.minreorderlevel AS  ReorderQty ,ScheduleDrug AS  Schedule,ELStock AS  EmergStock " & _
            '             " FROM mpItem INNER JOIN  mManufacturer ON mManufacturer.mfctrID = mpItem.mfctrID " & _
            '             " ORDER BY "

            'SELECT P.[Id],P.[ProductCode] AS Code,P.[ProductName] AS Name,S.SupplierName AS Company,R.PricePerPack AS Rate 
            StrSql = "SELECT C.Id,C.ColorCode,C.ColorDescription FROM [NGSPaintsDB].[dbo].[Color] C INNER JOIN [NGSPaintsDB].[dbo].[ProductColor] P ON (P.COLORID  =C.ID) WHERE C.IsDeleted=0  AND P.ProductID=" & intProductID.ToString() & " ORDER BY "
            ''StrSql = "SELECT C.Id,C.ColorCode,C.ColorDescription,S.Quantity FROM [NGSPaintsDB].[dbo].[Color] C INNER JOIN [NGSPaintsDB].[dbo].[ProductColor] P ON (P.COLORID  =C.ID) INNER JOIN [NGSPaintsDB].[dbo].[tStock] S ON(P.ID= S.ItemID AND C.ID=S.CLRCode) WHERE C.IsDeleted=0  AND P.ProductID=" & intProductID.ToString() & " ORDER BY "
            Dim StrRecordCntSql As String = "SELECT Count(ID) FROM [NGSPaintsDB].[dbo].[Color]"


            'If intColumnIndex = 1 Then
            '    StrSql = StrSql & " ColorCode"
            'Else : StrSql = StrSql & " ColorDescription"
            'End If
            StrSql = StrSql & " ColorDescription"
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@Orderby", IIf(intColumnIndex = 1, "Code", "Name"))

            RecordCount = DbHelper.ExecuteScalar(StrRecordCntSql)
            Return DbHelper.ExecuteDataReader(StrSql)

            'Call DbHelper.ExecuteDataReader(StrSql, Dtr)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemList", StrSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetGoDownList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id],[GodownCode],[GodownName] FROM [NGSPaintsDB].[dbo].[Godown] WHERE IsDeleted=0")
    End Function

    'Public Shared Function GetSiteList() As DataTable
    '    Return DbHelper.ExecuteDataTable(" SELECT [Id],[SiteCode],[SiteName] FROM [NGSPaintsDB].[dbo].[Site] WHERE [IsDeleted]=0")
    'End Function
    Public Shared Function GetPriceList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [ID],[PRICELISTCODE],[PRICELISTNAME] FROM [NGSPAINTSDB].[DBO].[PRICELIST] WHERE [ISDELETED]=0")
    End Function
    Public Shared Function GetPackList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id] AS PACKID ,([PackingContain] + ' '+ [PackingCode]+ ' '+ [PackingDescription]) AS PACK  FROM [NGSPaintsDB].[dbo].[Packing] WHERE [IsDeleted]=0")

    End Function

    Public Shared Function GetOtherCharges() As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT Id,ChargeName,'0' AS Value,0.0 AS Amount,IsTaxable FROM OtherCharge WHERE IsDeleted =0 AND AddToInvoice =1"
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetOtherCharges", strSql)
            Return Nothing
        End Try
    End Function
#Region "CommentedCode"

    'Public Shared Function SaveFinalBill(ByRef ObjPurchase As CPurchase, ByRef Bills() As CPurchaseBills) As Integer
    '    Dim strSql As New StringBuilder
    '    Dim dbConnection As New dbAccess
    '    Dim param(22) As SqlParameter
    '    Dim intResult As Integer = 0
    '    Dim PurchaseID As Integer

    '    Try
    '        dbConnection.BeginTransaction()
    '        strSql.Append("INSERT INTO tPurchase(PurchaseID, SeqPurchaseID, PartyCode, PurchaseDate, PromoterID, Amount, TotalTaxes,Discount, GrossAmount, PaidAmount, debitAmount, creditAmount, PayMode, DueDate, Status, CU, CD, LU, LD,PurchaseType,DrId,FinYrID,FinalBillID) " & _
    '                 "VALUES(@PurchaseID,@SeqPurchaseID,@PartyCode,@PurchaseDate,@PromoterID,@Amount,@TotalTaxes,@Discount,@GrossAmount,@PaidAmount,@debitAmount,@creditAmount,@PayMode,@DueDate,@Status,@CU,@CD,@LU,@LD,@PurchaseType,@DrId,@FinYrID,@FinalBillID) ")

    '        PurchaseID = DGeneral.GetSeqNum("Purchase", dbConnection, True) 'IIf(ObjPurchase.PartyCode = 0, "Purchase", "PurchasePRINT"), dbConnection)
    '        Dim SeqID As String = DGeneral.GetSeqID("PurchaseBILL", dbConnection)
    '        SeqID = SeqID & IIf(ObjPurchase.PartyID = 0, PurchaseID, DGeneral.GetSeqNum("PurchaseBILL", dbConnection, True))

    '        param(0) = New SqlParameter("@PurchaseID", PurchaseID)
    '        param(1) = New SqlParameter("@SeqPurchaseID", SeqID)
    '        param(2) = New SqlParameter("@PartyCode", ObjPurchase.PartyID)
    '        param(3) = New SqlParameter("@PurchaseDate", ObjPurchase.PurchaseDate)
    '        param(4) = New SqlParameter("@PromoterID", ConfigurationManager.AppSettings("DefaultCounterNo"))
    '        param(5) = New SqlParameter("@Amount", ObjPurchase.Amount)
    '        param(6) = New SqlParameter("@TotalTaxes", ObjPurchase.TotalTax)
    '        param(7) = New SqlParameter("@Discount", ObjPurchase.Discount)
    '        param(8) = New SqlParameter("@GrossAmount", ObjPurchase.GrossAmt)

    '        param(10) = New SqlParameter("@debitAmount", ObjPurchase.DebitAmt)
    '        param(11) = New SqlParameter("@creditAmount", ObjPurchase.CreditAmt)
    '        param(12) = New SqlParameter("@PayMode", "1")
    '        param(13) = New SqlParameter("@DueDate", ObjPurchase.PurchaseDate)
    '        param(14) = New SqlParameter("@Status", "A")
    '        param(15) = New SqlParameter("@CU", CCommon.GetUserID)
    '        param(16) = New SqlParameter("@CD", Date.Now.Date())
    '        param(17) = New SqlParameter("@LU", CCommon.GetUserID)
    '        param(18) = New SqlParameter("@LD", Date.Now.Date())
    '        param(19) = New SqlParameter("@PurchaseType", ObjPurchase.PurchaseType)
    '        param(20) = New SqlParameter("@DrID", ObjPurchase.DrID)
    '        param(21) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)
    '        param(22) = New SqlParameter("@FinalBillID", "F")
    '        intResult = dbConnection.ExecuteNonQuery(strSql.ToString(), param)

    '        If intResult <= 0 Then Exit Function

    '        strSql.Remove(0, strSql.Length)
    '        strSql.Append(" Update tPurchase Set FinalBillID =" & PurchaseID & " WHERE PurchaseID in (" & ObjPurchase.PurchaseBills & " )")

    '        intResult = dbConnection.ExecuteNonQuery(strSql.ToString())

    '        If intResult <= 0 Then Exit Function
    '        strSql.Remove(0, strSql.Length)
    '        strSql.Append("Update tPurchase Set  GrossAmount=GrossAmount- @Discount, Discount=Discount + @Discount Where PurchaseID=@PurchaseID")
    '        Dim UpdatPara(1) As SqlParameter

    '        For intCount As Integer = 0 To Bills.Length - 1


    '            UpdatPara(0) = New SqlParameter("@Discount", Bills(intCount).NewDiscount)
    '            UpdatPara(1) = New SqlParameter("@PurchaseID", Bills(intCount).PurchaseBillID)
    '            intResult = dbConnection.ExecuteNonQuery(strSql.ToString(), UpdatPara)
    '            If intResult <= 0 Then Exit Function
    '            If ObjPurchase.PartyID > 0 AndAlso Bills(intCount).PaidAmt > 0 Then
    '                Dim Payment(0) As CPayment
    '                Payment(0) = New CPayment
    '                With Payment(0)
    '                    .PayType = "Recp"
    '                    .PayerID = ObjPurchase.PartyID
    '                    .PayDate = Now.Date()
    '                    .PayMode = 1
    '                    .PayAmount = Bills(intCount).PaidAmt
    '                    .Concession = 0
    '                    .Description = "CASH CUMSTOMER Purchase"
    '                    .BillID = Bills(intCount).PurchaseBillID
    '                End With
    '                intResult = DPayment.SavePayment(Payment, "S", dbConnection)
    '                If intResult <= 0 Then Exit Function
    '            End If
    '        Next
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SaveFinalBill")
    '    Finally
    '        If intResult = 0 Then
    '            PurchaseID = 0
    '            dbConnection.RollBackTransaction()
    '        Else : dbConnection.CommitTransaction()
    '        End If
    '        dbConnection.closeSqlcon()
    '        SaveFinalBill = PurchaseID
    '    End Try
    'End Function

#End Region

    '#Region "COUNTER Purchase"

    '    Public Shared Function UpdateStock(ByVal StockID As Integer, ByVal Quantity As Integer, ByVal SchemeQty As Integer, ByVal PlusOrMinus As Char, ByRef dbConnection As dbAccess) As Integer
    '        Dim param(2) As SqlParameter
    '        Dim strSql As String = ""
    '        Try
    '            If PlusOrMinus = "P" Then
    '                strSql = "UPDATE tStock SET Quantity=Quantity + @Quantity , SchemeItemQty=SchemeItemQty + @SchemeItemQty WHERE StockID=@StockID"
    '            ElseIf PlusOrMinus = "M" Then
    '                strSql = "UPDATE tStock SET Quantity=Quantity - @Quantity , SchemeItemQty=SchemeItemQty - @SchemeItemQty WHERE StockID=@StockID AND Quantity >= @Quantity "
    '            End If
    '            param(0) = New SqlParameter("@Quantity", Quantity)
    '            param(1) = New SqlParameter("@StockID", StockID)
    '            param(2) = New SqlParameter("@SchemeItemQty", SchemeQty)
    '            Return dbConnection.ExecuteNonQuery(strSql, param)
    '        Catch ex As Exception
    '            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "UpdateStock", strSql)
    '            Return 0
    '        End Try
    '    End Function

    '    Public Shared Function GetBatchStockID(ByVal ItemID As Integer, ByVal CLRcode As String, ByVal dbConection As dbAccess) As DataTable
    '        Dim strSql As String = ""
    '        Try
    '            strSql = "SELECT StockID,Quantity,SchemeItemQty FROM tStock WHERE Quantity > 0 AND ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'"
    '            Return dbConection.ExecuteDataTable(strSql)
    '        Catch ex As Exception
    '            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "GetBatchStockID", strSql)
    '            Return Nothing
    '        End Try
    '    End Function

    '    'Public Shared Function InsertPurchaseItem(ByRef Item As cPurchaseItem, Optional ByRef dbConn As dbAccess = Nothing) As Integer
    '    '    Dim strSql As String = ""
    '    '    Dim sDStockIDs As New StringBuilder
    '    '    Dim param(14) As SqlParameter
    '    '    Dim dbConnection As New dbAccess
    '    '    Dim btResult As Byte
    '    '    Dim intQuantity As Integer = Item.Quantity
    '    '    Try
    '    '        If IsNothing(dbConn) Then dbConnection.BeginTransaction()
    '    '        With Item

    '    '            If DPurchaseInvoice.saveStockSummary(.ItemID, .Quantity, .SchemeQuantity, 0, "M", IIf(IsNothing(dbConn), dbConnection, dbConn), True) > 0 Then
    '    '                'Chandrakant :- THE VARIABLES CODE ADDED FOR UPDATING THE STOCK VENDOR 
    '    '                'SCHENARIO: IF SAME BATCH IS PURCHASED FROM DIFF VENDORS THEN STOCK WILL BE UPDATED AS PER VENDOR ONLY
    '    '                'THIS EXTRA CODE IS WORK FOR SAME BATCH AND SAME PRICE DETAILS HAVING DIFF VENDORS
    '    '                'SAME BATCH BUT DIFF PRICE DETAILS ARE HANDLED ON SCREEN FORMS(Purchase & PurchasePRINT)

    '    '                Dim dtbBatchStock As DataTable = GetBatchStockID(.ItemID, .ColorCode, IIf(IsNothing(dbConn), dbConnection, dbConn)) 'StockID,Quantity 
    '    '                'StockID,Quantity,SchemeItemQty
    '    '                Dim intCount As Integer = 0
    '    '                Dim intStockQty As Integer = 0
    '    '                Dim intSchemStockQty As Integer = 0


    '    '                While .Quantity > 0 And intCount < dtbBatchStock.Rows.Count
    '    '                    If .Quantity <= dtbBatchStock.Rows(intCount).Item(1) Then
    '    '                        intStockQty = .Quantity
    '    '                        .Quantity = 0
    '    '                    ElseIf .Quantity > dtbBatchStock.Rows(intCount).Item(1) Then
    '    '                        intStockQty = dtbBatchStock.Rows(intCount).Item(1)
    '    '                        .Quantity = .Quantity - dtbBatchStock.Rows(intCount).Item(1)
    '    '                    End If

    '    '                    If .SchemeQuantity > 0 Then
    '    '                        If .SchemeQuantity <= dtbBatchStock.Rows(intCount).Item(2) Then
    '    '                            intSchemStockQty = .Quantity
    '    '                            .SchemeQuantity = 0
    '    '                        ElseIf .SchemeQuantity > dtbBatchStock.Rows(intCount).Item(2) Then
    '    '                            intSchemStockQty = dtbBatchStock.Rows(intCount).Item(2)
    '    '                            .SchemeQuantity = .SchemeQuantity - dtbBatchStock.Rows(intCount).Item(2)
    '    '                        End If
    '    '                    Else : intSchemStockQty = 0
    '    '                    End If

    '    '                    If UpdateStock(dtbBatchStock.Rows(intCount).Item(0), intStockQty, intSchemStockQty, "M", IIf(IsNothing(dbConn), dbConnection, dbConn)) > 0 Then
    '    '                        sDStockIDs.Append(dtbBatchStock.Rows(intCount).Item(0).ToString() & "-" & intStockQty & "-" & intSchemStockQty & ",")
    '    '                    Else
    '    '                        btResult = 0
    '    '                        Exit Function
    '    '                    End If
    '    '                    intCount += 1
    '    '                End While
    '    '                intStockQty = Nothing
    '    '                intCount = Nothing
    '    '                If sDStockIDs.Length > 0 Then
    '    '                    sDStockIDs.Remove(sDStockIDs.Length - 1, 1) 'TO REMOVE LAST COOMMA ,
    '    '                    .StockDetails = sDStockIDs.ToString()
    '    '                    sDStockIDs = Nothing
    '    '                Else  'NO BATCH DETAILS ARE FOUND OR NO UPDATED STOCK DETAILS ARE AVAILABLE
    '    '                    btResult = 0
    '    '                    Exit Function
    '    '                End If

    '    '            Else
    '    '                btResult = 0
    '    '                Exit Function
    '    '            End If

    '    '            strSql = "INSERT INTO wPurchaseDet(SeqID,CounterID,ItemID,CLRcode,PurchasePrice,Qty,Amount,SchemeID,schemeItemQty, " & _
    '    '                     "SchemeDiscount,FreeReplacement,Status,CU,CD,StockID) " & _
    '    '                     "VALUES(@SeqID,@CounterID,@ItemID,@CLRcode,@PurchasePrice,@Qty,@Amount,@SchemeID,@schemeItemQty, " & _
    '    '                     "@SchemeDiscount,@FreeReplacement,@Status,@CU,@CD,@StockID) "
    '    '            .CounterSeqID = DGeneral.GetSeqNum("WPurchase", dbConnection, True)
    '    '            param(0) = New SqlParameter("@SeqID", .CounterSeqID)
    '    '            param(1) = New SqlParameter("@CounterID", .Counter)
    '    '            param(2) = New SqlParameter("@ItemID", .ItemID)
    '    '            param(3) = New SqlParameter("@CLRcode", .ColorCode)
    '    '            param(4) = New SqlParameter("@PurchasePrice", .PurchaseRate)
    '    '            param(5) = New SqlParameter("@Qty", intQuantity)
    '    '            param(6) = New SqlParameter("@Amount", .Amount)
    '    '            param(7) = New SqlParameter("@SchemeID", .SchemeID)
    '    '            param(8) = New SqlParameter("@schemeItemQty", .SchemeQuantity)
    '    '            param(9) = New SqlParameter("@SchemeDiscount", .SchemeDiscount)
    '    '            param(10) = New SqlParameter("@FreeReplacement", 0) ''Not Considered in Retailer so 0 is entered as defaultValue
    '    '            param(11) = New SqlParameter("@Status", "W")
    '    '            param(12) = New SqlParameter("@CU", CCommon.GetUserID)
    '    '            param(13) = New SqlParameter("@CD", Date.Now.Date())
    '    '            param(14) = New SqlParameter("@StockID", .StockDetails)
    '    '            If IsNothing(dbConn) Then
    '    '                btResult = dbConnection.ExecuteNonQuery(strSql, param)
    '    '            Else
    '    '                btResult = dbConn.ExecuteNonQuery(strSql, param)
    '    '            End If
    '    '        End With
    '    '    Catch ex As Exception
    '    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "InsertPurchaseItem")
    '    '        btResult = 0
    '    '    Finally
    '    '        If IsNothing(dbConn) Then
    '    '            If btResult > 0 Then
    '    '                dbConnection.CommitTransaction()
    '    '            Else
    '    '                dbConnection.RollBackTransaction()
    '    '            End If
    '    '            dbConnection.closeSqlcon()
    '    '        End If
    '    '        InsertPurchaseItem = btResult
    '    '    End Try
    '    'End Function

    '    'Public Shared Function UpdatePurchaseItem(ByRef Item As cPurchaseItem) As Integer
    '    '    Dim btResult As Byte
    '    '    Dim dbConnection As New dbAccess
    '    '    Try
    '    '        dbConnection.BeginTransaction()
    '    '        btResult = DeletePurchaseItem(Item.CounterSeqID, dbConnection)
    '    '        If btResult > 0 Then
    '    '            btResult = InsertPurchaseItem(Item, dbConnection)
    '    '        Else : btResult = 0
    '    '        End If
    '    '    Catch ex As Exception
    '    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "UpdatePurchaseItem")
    '    '        btResult = 0
    '    '    Finally
    '    '        If btResult > 0 Then
    '    '            dbConnection.CommitTransaction()
    '    '        Else
    '    '            dbConnection.RollBackTransaction()
    '    '        End If
    '    '        dbConnection.closeSqlcon()
    '    '        UpdatePurchaseItem = btResult
    '    '    End Try
    '    'End Function
    '    'Public Shared Function DeletePurchaseItem(ByVal CounterSeqID As Integer) As Integer
    '    '    Dim btResult As Byte
    '    '    Dim dbConnection As New dbAccess
    '    '    Dim strSql As String = ""
    '    '    Try
    '    '        dbConnection.BeginTransaction()
    '    '        Dim dtbWItems As DataTable = GetWItem(CounterSeqID) ', dbConnection)
    '    '        If dtbWItems.Rows.Count > 0 Then
    '    '            Dim drWItem As DataRow = dtbWItems.Rows(0) 'ItemID,CLRcode,Qty,Amount,schemeItemQty,StockID
    '    '            If DPurchaseInvoice.saveStockSummary(drWItem.Item(0), drWItem.Item(2), drWItem.Item(4), 0, "P", dbConnection, True) > 0 Then
    '    '                Dim strStocks() As String = drWItem.Item(5).split(",")
    '    '                Dim strStockDtls() As String
    '    '                For intCount As Integer = 0 To strStocks.Length - 1
    '    '                    strStockDtls = strStocks(intCount).Split("-")  'StockID-Quantity-SchmQty
    '    '                    btResult = UpdateStock(strStockDtls(0), strStockDtls(1), strStockDtls(2), "P", dbConnection)   'SchemeQuantity IS 0 
    '    '                    If btResult = 0 Then Exit Function
    '    '                Next
    '    '                If btResult > 0 Then
    '    '                    strSql = " DELETE FROM wPurchaseDet WHERE SeqID=" & CounterSeqID

    '    '                    btResult = dbConnection.ExecuteNonQuery(strSql)
    '    '                End If
    '    '            End If
    '    '        Else
    '    '            btResult = 1
    '    '        End If
    '    '    Catch ex As Exception
    '    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "DeletePurchaseItem1", strSql)
    '    '        btResult = 0
    '    '    Finally
    '    '        If btResult > 0 Then
    '    '            dbConnection.CommitTransaction()
    '    '        Else
    '    '            dbConnection.RollBackTransaction()
    '    '        End If
    '    '        dbConnection.closeSqlcon()
    '    '        DeletePurchaseItem = btResult
    '    '    End Try
    '    'End Function

    '    'Public Shared Function DeletePurchaseItem(ByVal CounterSeqID As Integer, ByRef dbConn As dbAccess) As Integer
    '    '    Dim btResult As Byte

    '    '    Dim strSql As String = ""
    '    '    Try

    '    '        Dim dtbWItems As DataTable = GetWItem(CounterSeqID) ', dbConnection)
    '    '        If dtbWItems.Rows.Count > 0 Then
    '    '            Dim drWItem As DataRow = dtbWItems.Rows(0) 'ItemID,CLRcode,Qty,Amount,schemeItemQty,StockID
    '    '            If DPurchaseInvoice.saveStockSummary(drWItem.Item(0), drWItem.Item(2), drWItem.Item(4), 0, "P", dbConn, True) > 0 Then
    '    '                Dim strStocks() As String = drWItem.Item(5).split(",")
    '    '                Dim strStockDtls() As String
    '    '                For intCount As Integer = 0 To strStocks.Length - 1
    '    '                    strStockDtls = strStocks(intCount).Split("-")  'StockID-Quantity-SchmQty
    '    '                    btResult = UpdateStock(strStockDtls(0), strStockDtls(1), strStockDtls(2), "P", dbConn)
    '    '                    If btResult = 0 Then Exit Function
    '    '                Next
    '    '                If btResult > 0 Then
    '    '                    strSql = " DELETE FROM wPurchaseDet WHERE SeqID=" & CounterSeqID
    '    '                    btResult = dbConn.ExecuteNonQuery(strSql)
    '    '                End If
    '    '            End If
    '    '        Else
    '    '            btResult = 1
    '    '        End If
    '    '    Catch ex As Exception
    '    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "DeletePurchaseItem2", strSql)
    '    '        btResult = 0
    '    '    Finally
    '    '        DeletePurchaseItem = btResult
    '    '    End Try
    '    'End Function

    '    'Public Shared Function SavePurchaseItem(ByRef Item As cPurchaseItem, ByVal SaveOrUpdate As Char) As Integer
    '    '    Try

    '    '        Select Case SaveOrUpdate
    '    '            Case "W" 'Insert
    '    '                Return InsertPurchaseItem(Item)
    '    '            Case "U" 'Update
    '    '                Return UpdatePurchaseItem(Item)
    '    '            Case "D" 'Delete
    '    '                Return DeletePurchaseItem(Item.CounterSeqID)
    '    '        End Select
    '    '    Catch ex As Exception
    '    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchaseItem")
    '    '        SavePurchaseItem = 0
    '    '    End Try
    '    'End Function

    '    'Public Shared Function FreezeItems(ByVal Items As String) As Integer
    '    '    Dim strSql As String = ""
    '    '    Try
    '    '        If Items.Length = 0 Then Return 1

    '    '        strSql = "UPDATE wPurchaseDet SET  Status = 'F' WHERE SeqID IN (" & Items & ")"
    '    '        Return DbHelper.ExecuteNonQuery(strSql)

    '    '    Catch ex As Exception
    '    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "FreezeItems", strSql)
    '    '        Return 0
    '    '    End Try
    '    'End Function

    '    'Public Shared Function DeleteItems(ByVal ItemWSeqNo As String) As Integer
    '    '    'Dim sbSql As New StringBuilder
    '    '    Dim strItems() As String = ItemWSeqNo.Split(",")
    '    '    Dim intReslult As Integer = 0
    '    '    Try

    '    '        For intCount As Integer = 0 To strItems.Length - 1
    '    '            If Trim(strItems(intCount)) = "" Then Continue For
    '    '            If DeletePurchaseItem(strItems(intCount)) > 0 Then
    '    '                intReslult += 1
    '    '            Else
    '    '                intReslult = 0
    '    '                Exit Function
    '    '            End If

    '    '        Next
    '    '    Catch ex As Exception
    '    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "DeleteItems")
    '    '        Return 0
    '    '    Finally
    '    '        DeleteItems = intReslult
    '    '    End Try
    '    'End Function

    '    'Public Shared Function DeleteNewItems(ByVal Items As String, ByVal CounterNo As Integer) As Integer
    '    '    Dim strSql As String = ""
    '    '    Try
    '    '        If Items.Length = 0 Then Return 1

    '    '        strSql = "DELETE FROM WPurchaseDET WHERE CounterID= " & CounterNo & "AND SeqID NOT IN (" & Items & ")"
    '    '        Return DbHelper.ExecuteNonQuery(strSql)

    '    '    Catch ex As Exception
    '    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "DeleteNewItems", strSql)
    '    '        Return 0
    '    '    End Try
    '    'End Function

    '    'Public Shared Function ShiftCounter(ByVal Items As String, ByVal CounterNo As Integer) As Integer
    '    '    Dim strSql As String = ""
    '    '    Try
    '    '        If Items.Length = 0 Then Return 1

    '    '        strSql = "UPDATE wPurchaseDet SET  CounterID =" & CounterNo & " WHERE SeqID IN (" & Items & ")"
    '    '        Return DbHelper.ExecuteNonQuery(strSql)

    '    '    Catch ex As Exception
    '    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "ShiftCounter", strSql)
    '    '        Return 0
    '    '    End Try
    '    'End Function

    '    'Public Shared Function ClearCounterItem(ByVal SeqID As Integer, ByRef dbconnection As dbAccess) As Integer
    '    '    Dim strSql As String = ""
    '    '    Try
    '    '        strSql = "DELETE FROM wPurchaseDet WHERE SeqID=" & SeqID
    '    '        Return dbconnection.ExecuteNonQuery(strSql)
    '    '    Catch ex As Exception
    '    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "ClearCounterItem", strSql)
    '    '        Return 0
    '    '    End Try
    '    'End Function

    '#End Region

    'Public Shared Function Save(ByRef ObjPurchase As CPurchase) As Integer
    '    Dim dbConnection As New dbAccess
    '    Dim intResult As Integer = 0
    '    Dim intPurchaseID As Integer = 0
    '    Try
    '        dbConnection.BeginTransaction()
    '        intPurchaseID = SavePurchase(ObjPurchase, dbConnection)
    '        If intPurchaseID > 0 Then
    '            intResult = SavePurchaseItemDetail(intPurchaseID, ObjPurchase.PromoterID, dbConnection)
    '        End If
    '        If intResult = 0 Then Exit Function
    '        dbConnection.CommitTransaction()
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "Save")
    '        intResult = 0
    '    Finally
    '        If intResult = 0 Then dbConnection.RollBackTransaction()
    '        dbConnection.closeSqlcon()
    '        Save = intPurchaseID
    '    End Try
    'End Function

    'Public Shared Function SavePurchaseItemDetail(ByVal PurchaseID As Integer, ByRef dbConnection As dbAccess) As Integer
    '    Dim strSql As String = String.Empty
    '    Dim param(14) As SqlParameter
    '    Dim dtDItem As DataTable
    '    Dim i As Integer
    '    'Dim SRegID As String
    '    Try

    '        ' Dim dtDCounterItems As DataTable = GetCounterData(CounterID)
    '        'SELECT SEQID,ITEMID,CLRcode,PurchaseUNIT,PurchasePRICE,QTY,AMOUNT,SCHEMEID,SCHEMEITEMQTY,SCHEMEDISCOUNT,FREEREPLACEMENT,STOCKUPDATE,STATUS 
    '        For Each Item As DataRow In dtDCounterItems.Rows
    '            strSql = " INSERT INTO tPurchaseDet(PurchaseID,ItemID,CLRcode,ExpDate,PurchasePack,ItemsPerPack,PurchaseUnit,PurchasePrice," & _
    '                     "Qty,Amount,SchemeID,schemeItemQty,SchemeDiscount,FreeReplacement,FinYrId)" & _
    '                     " VALUES(@PurchaseID,@ItemID,@CLRcode,@ExpDate,@PurchasePack,@ItemsPerPack,@PurchaseUnit,@PurchasePrice," & _
    '                     "@Qty,@Amount,@SchemeID,@schemeItemQty,@SchemeDiscount,@FreeReplacement,@FinYrId)"


    '            param(0) = New SqlParameter("@PurchaseID", PurchaseID)
    '            param(1) = New SqlParameter("@ItemID", Item("ITEMID"))
    '            param(2) = New SqlParameter("@CLRcode", Item("CLRcode"))


    '            dtDItem = GetItemPackUnit(Item("ITEMID"), Item("CLRcode"))
    '            'SellPack,SellUnit,ExpDate,ConversionFactor 
    '            param(3) = New SqlParameter("@ExpDate", dtDItem.Rows(0).Item("ExpDate"))
    '            param(4) = New SqlParameter("@PurchasePack", dtDItem.Rows(0).Item("SellPack"))
    '            param(5) = New SqlParameter("@ItemsPerPack", dtDItem.Rows(0).Item("ConversionFactor"))
    '            param(6) = New SqlParameter("@PurchaseUnit", Item("PurchaseUNIT"))
    '            param(7) = New SqlParameter("@PurchasePrice", Item("PurchasePRICE"))
    '            param(8) = New SqlParameter("@Qty", Item("QTY"))
    '            param(9) = New SqlParameter("@Amount", Item("AMOUNT"))
    '            param(10) = New SqlParameter("@SchemeID", Item("SCHEMEID"))
    '            param(11) = New SqlParameter("@schemeItemQty", Item("SCHEMEITEMQTY"))
    '            param(12) = New SqlParameter("@SchemeDiscount", Item("SCHEMEDISCOUNT"))
    '            param(13) = New SqlParameter("@FreeReplacement", Item("FREEREPLACEMENT"))
    '            param(14) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)

    '            i = i + dbConnection.ExecuteNonQuery(strSql, param)
    '            If ConfigurationManager.AppSettings("TAX") = 1 Then 'CHECK FOR TAX (T0)
    '                SavePurchaseTaxDetails(PurchaseID, Item("ITEMID"), Item("CLRcode"), Item("AMOUNT"), dbConnection)
    '            End If
    '            ClearCounterItem(Item("SEQID"), dbConnection)
    '            DPurchaseInvoice.SaveStockRegister(Item("ITEMID"), Item("QTY"), Now.Date, "S", dbConnection)
    '        Next
    '        Return i
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchase", "SavePurchaseItemDetail", strSql)
    '        Return 0
    '    End Try
    'End Function

End Class